﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using University.Workshop.Model;

namespace University.Workshop.DataAccess
{
    public static class WebmasterDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static Webmaster InsertWebmaster(Webmaster newWebmaster)
        {
            if (newWebmaster != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [WEBMASTER](UserId, CanRespondTickets, CanSetWebMasters)" +
                                       " VALUES(@userId, @canRespondTickets, @canSetWebMasters)";
                    cmd.Parameters.AddWithValue("userId", newWebmaster.UserId);
                    cmd.Parameters.AddWithValue("canRespondTickets",newWebmaster.CanRespondTickets);
                    cmd.Parameters.AddWithValue("canSetWebMasters", newWebmaster.CanSetWebmasters);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a webmaster");
                    }
                    newWebmaster.Id = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return newWebmaster;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting a webmaster", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static Webmaster GetWebmasterByUserId(long userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [WEBMASTER] Where UserId = @userId";
                cmd.Parameters.AddWithValue("userId", userId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Webmaster webmaster = new Webmaster();

                while (reader.Read())
                {
                    webmaster.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    webmaster.UserId = Int64.Parse(reader["UserId"].ToString(), CultureInfo.CurrentCulture);
                    webmaster.CanRespondTickets = string.IsNullOrEmpty(reader["CanRespondTickets"].ToString()) ? false : bool.Parse(reader["CanRespondTickets"].ToString());
                    webmaster.CanSetWebmasters = string.IsNullOrEmpty(reader["CanSetWebMasters"].ToString()) ? false : bool.Parse(reader["CanSetWebMasters"].ToString());
                }
                return webmaster;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a webmaster", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
